<?php
class m_job extends Zmodel{
	function get_company($num, $offset){
		$this->load->database();
		$sqlstatement="SELECT com_id,
        			 com_name,
        			 com_phone,
        			 com_email,
        			 com_contactname,
        			 com_website,
        			 com_logo,
            		com_logo_size,
            		 com_logo_name,
            		 com_logo_type
            	FROM companies
            	ORDER BY com_id DESC
            	 LIMIT ".$num;
		if($offset==""){
			$sql=$sqlstatement." OFFSET 0";
		}else{
			$sql=$sqlstatement ." OFFSET ". $offset;
		}
		
		return $this->db->query($sql);
	}
	function count_companies(){
		$this->load->database();
		$sql="SELECT com_id,com_name FROM companies ORDER BY com_name ASC";
		return $this->db->query($sql);
	}
	function get_company_by_comid($comid){
		$this->load->database();
		$sql="SELECT com_id,
        			 com_name,
        			 com_desc,
        			 com_address,
        			 com_phone,
        			 com_fax,
        			 com_email,
        			 com_contactname,
        			 com_website,
            		 com_logo,
            		 com_logo_size,
            		 com_logo_name,
            		 com_logo_type
            	FROM companies
               WHERE com_id=".$comid;
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function get_company_maxid(){
		$this->load->database();
		$sql="SELECT com_id
            	FROM companies ORDER BY com_id DESC LIMIT 1";
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function get_company_by_announid($announid){
		$this->load->database();
		$sql="SELECT com_id
            	FROM announcements
               WHERE announ_id=".$announid;
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function get_company_by_name($comname){
		$this->load->database();
	 $sql="SELECT com_id,com_name
            	FROM companies
               WHERE com_name='".pg_escape_string($comname)."'";
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function insert_new_company($name,$desc,$address,$phone,$fax,$email,$contact,$website,$comlogo,$logosize,$comlogoname,$logotype){
		$this->load->database();
		$sql="INSERT INTO companies(
            				  com_name,
            				  com_desc,
            				  com_address,
            				  com_phone,
            				  com_fax,
            				  com_email,
            				  com_contactname,
            				  com_website,
            				  com_logo,
            				  com_logo_size,
            				  com_logo_name,
            				  com_logo_type) 
            	  VALUES('".pg_escape_string($name)."',
		            	  '".pg_escape_string($desc)."',
		            	  '".pg_escape_string($address)."',
		            	  '".pg_escape_string($phone)."',
		            	  '".pg_escape_string($fax)."',
		            	  '".pg_escape_string($email)."',
		            	  '".pg_escape_string($contact)."',
		            	  '".pg_escape_string($website)."',
		            	  '".pg_escape_bytea($comlogo)."',
		            	   ".$logosize.",
		            	  '".pg_escape_string($comlogoname)."',
		            	  '".$logotype."')";
            $this->db->query($sql);
	}
	function update_company($name,$desc,$address,$phone,$fax,$email,$contact,$website,$id){
		$this->load->database();
		$sql=$sql="UPDATE companies SET com_name='".pg_escape_string($name)."',
            					   		com_desc='".pg_escape_string($desc)."',
            					   		com_address='".pg_escape_string($address)."',
		            					com_phone='".pg_escape_string($phone)."',
		            					com_fax='".pg_escape_string($fax)."',
		            					com_email='".pg_escape_string($email)."', 
		            					com_contactname='".pg_escape_string($contact)."',
		            					com_website='".pg_escape_string($website)."'		            					
            			     	  WHERE com_id=".$id;
        $this->db->query($sql);
	}
	
	function update_logo($comlogo,$logosize,$logoname,$logotype,$id){
		$this->load->database();
	    $sql="UPDATE companies SET com_logo='".pg_escape_bytea($comlogo)."',
		            			   com_logo_size= ".$logosize.",
		            			   com_logo_name='".pg_escape_string($logoname)."',
		            			   com_logo_type='".$logotype."'
	            			 WHERE com_id=".$id;				  
	    $this->db->query($sql);
	}
	function delete_company($id){
	    $this->load->database();
	    $sql="DELETE FROM companies 
	            	WHERE com_id=".$id;				  
	    $this->db->query($sql);
	  
	}

	//================= Announcement ==========================
	function count_announ(){
		$this->load->database();
		$sql="SELECT announ_id
			 	FROM announcements";
		return $this->db->query($sql);
	}
	function count_announ_by_comid($comid){
		$this->load->database();
		$sql="SELECT announ_id
			 	FROM announcements
			   WHERE com_id=".$comid;
		return $this->db->query($sql);
	}
	function get_announ_by_id($announid){
		$this->load->database();
		$sql="SELECT announ_id,
					 date_post,
					 date_close,
					 announ_notice
            	FROM announcements
               WHERE announ_id=".$announid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_announ_by_titleid($titleid){
	$this->load->database();
		$sql="SELECT announ_id
            	FROM titles
               WHERE title_id=".$titleid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_announ_by_comid($comid,$num, $offset){
		$this->load->database();
		$sqlstatement="SELECT a.announ_id,
	        			 c.com_id,
	        			 c.com_name,
	        			 a.date_post,
	        			 a.date_close
	            	FROM announcements AS a 
	          INNER JOIN companies AS c 
	          		  ON a.com_id=c.com_id";
		if($offset==""){
			if($comid=='0'){
				$sql=$sqlstatement." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}else {
				$sql=$sqlstatement." WHERE c.com_id=".$comid." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET 0";
			}
		}else{
			if($comid=='0'){
				$sql=$sqlstatement." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;
			}else {
				$sql=$sqlstatement." WHERE c.com_id=".$comid." ORDER BY a.announ_id DESC LIMIT ".$num." OFFSET ". $offset;
			}
		}
		
		return $this->db->query($sql);
	}
	
	function get_allannoun($num, $offset){
		$this->load->database();
		$sqlstatement="SELECT a.announ_id,
        			 c.com_id,
        			 c.com_name,
        			 c.com_desc,
        			 c.com_logo,
            		 c.com_logo_size,
            		 c.com_logo_name,
            		 c.com_logo_type,
        			 a.date_post,
        			 a.date_close
            	FROM announcements AS a 
          INNER JOIN companies AS c 
          		  ON a.com_id=c.com_id 
          		  ORDER BY a.announ_id DESC";
		if($offset==""){
			$sql=$sqlstatement." LIMIT ".$num." OFFSET 0";
		}else{
			$sql=$sqlstatement." LIMIT ".$num." OFFSET ". $offset;
		}
        
        return $this->db->query($sql);
	}
	function save_new_announ($datepost,$dateclose,$note,$comid){
		$this->load->database();
			$sql="INSERT INTO announcements(
	            				  date_post,
	            				  date_close,
	            				  announ_notice,
	            				  com_id) 
	            	   VALUES('".pg_escape_string($datepost)."',
			            	  '".pg_escape_string($dateclose)."',
			            	  '".pg_escape_string($note)."',
			            	   ".$comid.")";
	     $this->db->query($sql);
	}
	function save_update_announ($datepost,$dateclose,$note,$comid,$announid){
		$this->load->database();
		$sql=$sql="UPDATE announcements SET date_post='".pg_escape_string($datepost)."',
            					   			date_close='".pg_escape_string($dateclose)."',
            					   			announ_notice='".pg_escape_string($note)."',
		            						com_id=".($comid)."
            			     	 	  WHERE announ_id=".$announid;
        $this->db->query($sql);
	}
	function  delete_announ($announid){
		$this->load->database();
	    $sql="DELETE FROM announcements 
	            	WHERE announ_id=".$announid;				  
	    $this->db->query($sql);
	}
	function delete_title_annouid($announid){
		$this->load->database();
	    $sql="DELETE FROM titles 
	            	WHERE announ_id=".$announid;				  
	    $this->db->query($sql);
	}
	
	function get_hit_job($announid){
		$this->load->database();
		$sql="SELECT *
				FROM hit_jobs WHERE announ_id=".$announid;
		return $this->db->query($sql);
	}
	function addnew_hit_job($announid){
		$this->load->database();
		$sql="INSERT INTO hit_jobs(announ_id) VALUES(".$announid.")";
		$this->db->query($sql);
	}
	function delete_hit_job($announid){
		$this->load->database();
		$sql="DELETE FROM hit_jobs
				   WHERE  announ_id=".$announid;
		$this->db->query($sql);
	}
	
	//================== title ==================
	function get_title_by_announ($announid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles
               WHERE announ_id=".$announid."
               ORDER BY title_id DESC";
		
        return $this->db->query($sql);
	}
	function get_number_title($announid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles
            	WHERE announ_id=".$announid;
		return $this->db->query($sql);
	}
	function get_title_by_id($titleid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles
               WHERE title_id=".$titleid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_company_by_titleid($titleid){
		$this->load->database();
		$sql="SELECT announ_id
				FROM titles
               WHERE title_id=".$titleid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function insert_new_title($titledesc,$announid){
		$this->load->database();
		$sql="INSERT INTO titles(title_desc,
	            				 announ_id) 
	            	  	  VALUES('".pg_escape_string($titledesc)."',
			            	  ".$announid.")";
	     $this->db->query($sql);
	}
	function save_update_title($desc,$annouid,$titleid){
		$this->load->database();
		$sql=$sql="UPDATE titles SET title_desc='".pg_escape_string($desc)."',
            					   			announ_id=".($annouid)."
            			     	 	  WHERE title_id=".$titleid;
        $this->db->query($sql);
	}
	function  delete_title($titleid){
		$this->load->database();
	    $sql="DELETE FROM titles 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
	
	//===================== category =============================
	function get_all_category($num, $offset){
		$this->load->database();
		if($offset==""){
			$sql="SELECT *
            	FROM categories
            	ORDER BY cate_id ASC
            	LIMIT ".$num." OFFSET 0";
		}else{
			$sql="SELECT *
            	FROM categories
               LIMIT ".$num." OFFSET ". $offset;
		}
		
        return $this->db->query($sql);
	}
	function get_data_category(){
		$this->load->database();
		$sql="SELECT *
            	FROM categories
            	ORDER BY cate_desc ASC";
		
        return $this->db->query($sql);
	}
	function count_category(){
		$this->load->database();
		$sql="SELECT cate_id,cate_desc
            	FROM categories";
		return $this->db->query($sql);
	}
	function get_category_by_id($cateid){
		$this->load->database();
		$sql="SELECT *
            	FROM categories
               WHERE cate_id=".$cateid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_category_by_name($catename){
		$this->load->database();
		$sql="SELECT *
            	FROM categories
               WHERE cate_desc='".pg_escape_string($catename)."'";
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function iscategoryexist($id,$desc){
		$this->load->database();
		$sql="SELECT *
            	FROM categories
               WHERE cate_id<>".$id." 
               	 AND cate_desc='".pg_escape_string($desc)."'";
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function insert_new_category($desc){
		$this->load->database();
		$sql="INSERT INTO categories(cate_desc) 
	            	VALUES('".pg_escape_string($desc)."')";
	     $this->db->query($sql);
	}
	function save_update_category($desc,$cateid){
		$this->load->database();
		$sql=$sql="UPDATE categories SET cate_desc='".pg_escape_string($desc)."'
            			     	 	  WHERE cate_id=".$cateid;
        $this->db->query($sql);
	}
	function  delete_category($cateid){
		$this->load->database();
	    $sql="DELETE FROM categories 
	            	WHERE cate_id=".$cateid;				  
	    $this->db->query($sql);
	}
	
	//========================= Duty ==============================
function get_number_duty($titleid){
		$this->load->database();
		$sql="SELECT *
            	FROM duties
            	WHERE title_id=".$titleid." 
            	ORDER BY duty_desc ASC";
		return $this->db->query($sql);
	}
	function get_duty_by_id($id){
		$this->load->database();
		$sql="SELECT *
            	FROM duties
            	WHERE duty_id=".$id;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function insert_new_duty($dutydesc,$titleid){
		$this->load->database();
		$sql="INSERT INTO duties(duty_desc,title_id) 
	            	VALUES('".pg_escape_string($dutydesc)."',
	            			".$titleid."
	            		   )";
	     $this->db->query($sql);
	}
	function save_update_duty($dutyid,$dutydesc,$titleid){
		$this->load->database();
		$sql=$sql="UPDATE duties SET duty_desc='".pg_escape_string($dutydesc)."',
									 title_id=".$titleid."
            			     	 	  WHERE duty_id=".$dutyid;
        $this->db->query($sql);
	}
	function  delete_duty($dutyid){
		$this->load->database();
	    $sql="DELETE FROM duties 
	            	WHERE duty_id=".$dutyid;				  
	    $this->db->query($sql);
	}
	function  delete_duty_by_titleid($titleid){
		$this->load->database();
	    $sql="DELETE FROM duties 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
	
	//========================= Requirement =======================
	function get_number_requirement($titleid){
		$this->load->database();
		$sql="SELECT *
            	FROM requirements
            	WHERE title_id=".$titleid;
		return $this->db->query($sql);
	}
	function get_requirement_by_id($id){
		$this->load->database();
		$sql="SELECT *
            	FROM requirements
            	WHERE req_id=".$id;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_all_requirements(){
		$this->load->database();
		$sql="SELECT *
            	FROM requirements
            	ORDER BY req_desc ASC";
        return $this->db->query($sql);
	}
	function insert_new_requirement($reqdesc,$titleid){
		$this->load->database();
		$sql="INSERT INTO requirements(req_desc,title_id) 
	            	VALUES('".pg_escape_string($reqdesc)."',
	            			".$titleid."
	            		   )";
	     $this->db->query($sql);
	}
	function save_update_requirement($reqid,$reqdesc,$titleid){
		$this->load->database();
		$sql=$sql="UPDATE requirements SET req_desc='".pg_escape_string($reqdesc)."',
										   title_id=".$titleid."
            			     	 	 WHERE req_id=".$reqid;
        $this->db->query($sql);
	}
	function  delete_requirement($reqid){
		$this->load->database();
	    $sql="DELETE FROM requirements 
	            	WHERE req_id=".$reqid;				  
	    $this->db->query($sql);
	}
	function  delete_requirement_by_titleid($titleid){
		$this->load->database();
	    $sql="DELETE FROM requirements 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
	
	//========================= Location ==========================
	function get_all_locations(){
		$this->load->database();
		$sql="SELECT *
            	FROM locations";
        return $this->db->query($sql);
	}
	function get_location_by_locid($locid){
			$this->load->database();
			$sql="SELECT *
		            FROM locations
		           WHERE loc_id=".$locid;
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	
	//======================= titles_categories ===========================
	function istitlecategoryexist($titleid,$cateid){
		$this->load->database();
		$sql="SELECT *
		        FROM titles_categories
		       WHERE title_id=".$titleid." AND 
		       		 cate_id=".$cateid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_number_category($titleid){
		$this->load->database();
		$sql="SELECT cate_id
            	FROM titles_categories
               WHERE title_id=".$titleid;
		return $this->db->query($sql);
	}
	function save_addnew_titlecate($titleid,$cateid){
		$this->load->database();
		$sql="INSERT INTO titles_categories(title_id,cate_id) 
	            	VALUES(".$titleid.",".$cateid.")";
	     $this->db->query($sql);
	}
	function delete_titlecate_by_cateid($cateid){
		$this->load->database();
	    $sql="DELETE FROM titles_categories 
	            	WHERE cate_id=".$cateid;				  
	    $this->db->query($sql);
	}
	function delete_titlecate_by_titleid($titleid){
		$this->load->database();
	    $sql="DELETE FROM titles_categories 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
	//=============== titles_locations =========================
	function istitlelocationexist($titleid,$locid){
		$this->load->database();
		$sql="SELECT *
		        FROM titles_locations
		       WHERE title_id=".$titleid." AND 
		       		 loc_id=".$locid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_number_location($titleid){
		$this->load->database();
		$sql="SELECT loc_id
            	FROM titles_locations
               WHERE title_id=".$titleid;
		return $this->db->query($sql);
	}
	function save_addnew_titleloc($titleid,$locid){
		$this->load->database();
		$sql="INSERT INTO titles_locations(title_id,loc_id) 
	               VALUES(".$titleid.",".$locid.")";
	     $this->db->query($sql);
	}
	function delete_titleloc_by_locid($locid){
		$this->load->database();
	    $sql="DELETE FROM titles_locations 
	            	WHERE loc_id=".$locid;				  
	    $this->db->query($sql);
	}
	function delete_titleloc_by_titleid($titleid){
		$this->load->database();
	    $sql="DELETE FROM titles_locations 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
	
	//=========================== titles_schedule ====================
	function get_all_schedule(){
		$this->load->database();
		$sql="SELECT *
            	FROM schedules";
		return $this->db->query($sql);
	}
	
	function istitlescheduleexist($titleid,$scheduleid){
		$this->load->database();
		$sql="SELECT *
		        FROM titles_schedules
		       WHERE title_id=".$titleid." AND 
		       		 schedule_id=".$scheduleid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	function get_schedule_by_scheduleid($scheduleid){
		$this->load->database();
			$sql="SELECT *
		            FROM schedules
		           WHERE schedule_id=".$scheduleid;
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function get_number_schedule($titleid){
		$this->load->database();
		$sql="SELECT *
            	FROM titles_schedules
            	WHERE title_id=".$titleid;
		return $this->db->query($sql);
	}
	function save_addnew_titleschedule($titleid,$scheduleid){
		$this->load->database();
		$sql="INSERT INTO titles_schedules(title_id,schedule_id) 
	               VALUES(".$titleid.",".$scheduleid.")";
	     $this->db->query($sql);
	}
	function delete_titleschedule_by_scheduleid($scheduleid){
		$this->load->database();
	    $sql="DELETE FROM titles_schedules 
	            	WHERE schedule_id=".$scheduleid;				  
	    $this->db->query($sql);
	}
	function delete_titleschedule_by_titleid($titleid){
		$this->load->database();
	    $sql="DELETE FROM titles_schedules 
	            	WHERE title_id=".$titleid;				  
	    $this->db->query($sql);
	}
}
?>